package com.angus.modules.content.mapper;

import com.angus.modules.content.domain.SearchContent;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


@Mapper
public interface SearchContentMapper extends BaseMapper<SearchContent> {

    /**
     * 根据搜索词获取数据
     * @param searchWord
     * @return
     */
    @Select("<script>"+
            "SELECT " +
            "c.word, " +
            "c.group_flag, " +
            "c.group_id, " +
            "c.course_code, " +
            "c.create_time, " +
            "n.unit_num,  " +
            "n.is_pay " +
            "FROM " +
            "( " +
            "SELECT " +
            "cn_word AS word, " +
            "group_flag, " +
            "group_id, " +
            "course_code, " +
            "create_time  " +
            "FROM " +
            "course_cn_content  " +
            "WHERE " +
            "del_flag = 0  " +
            "AND group_flag NOT LIKE '%-s'  " +
            "AND cn_word LIKE CONCAT('%', #{searchWord}, '%') " +
            "AND EXISTS ( " +
            "SELECT " +
            "1  " +
            "FROM " +
            "sys_unit  " +
            "WHERE " +
            "group_id = course_cn_content.group_id  " +
            "AND course_code = course_cn_content.course_code  " +
            "AND del_flag = 0  " +
            ") UNION ALL " +
            "SELECT " +
            "en_word AS word, " +
            "group_flag, " +
            "group_id, " +
            "course_code, " +
            "create_time  " +
            "FROM " +
            "course_en_content  " +
            "WHERE " +
            "del_flag = 0  " +
            "AND group_flag NOT LIKE '%-s'  " +
            "AND en_word LIKE CONCAT('%', #{searchWord}, '%') " +
            "AND EXISTS ( " +
            "SELECT " +
            "1  " +
            "FROM " +
            "sys_unit  " +
            "WHERE " +
            "group_id = course_en_content.group_id  " +
            "AND course_code = course_en_content.course_code  " +
            "AND del_flag = 0  " +
            ")  " +
            ") c " +
            "INNER JOIN sys_unit AS n ON c.group_id = n.group_id  " +
            "AND c.course_code = n.course_code  " +
            "WHERE " +
            "n.del_flag = 0  " +
            "ORDER BY " +
            "c.create_time DESC"+
        "</script>")
    List<SearchContent> getSearchContentList(@Param("searchWord") String searchWord);

}
